capture	log close
log using MAR01-DataClean,  replace text

//  program:    Stata 
//  task:       Merging Data Together and Cleaning  
//  project:    Market

version
clear all
macro drop _all
set linesize 80
set more off
local tag "06-05-23 | Cleanup 06-06-2024"
local file "MAR01-DataClean"
local note "|`tag' | `file'"


//	#0
//	Loading Data
insheet using 00-Pridemore-Foundation-05-31-23.csv, comma clear n
rename Nation nation

gen CID=. 
do 000-CID-nation.do
tab nation if CID==. 
order CID nation year, first 
save 00-Fraser.dta, replace 


//	#1
//	SP data
insheet using OECD-SP-Public-Total-06-27-24.csv, comma clear n 
reshape long v,  j(year) i(Country)
rename Country nation 
rename v public_sp

gen CID=. 
do 00-CID-nation.do
tab nation if CID==.
order CID nation year, first 
save SPOECD.dta, replace 

//	#2
//	LaFree Globalization Data
use KOFGI_2023_public.dta, clear 
rename code countrycode
gen CID=.
do 00-CID-CountryCode.do
rename country nation 
replace CID=642 if countrycode=="ROM"
tab nation if CID==.
drop if CID==.
order CID nation year, first 
save KOFGI.dta, replace 

//	#3
// Homicide Data
use CT00-PridemoreMerge, clear 

//	#4
//	Merging data 
merge 1:1 CID year using 00-Fraser.dta
drop _merge 
merge 1:1 CID year using SP00-PridemoreMerge.dta
drop _merge 
merge 1:1 CID year using SPOECD.dta
drop  _merge 
merge 1:1 CID year using KOFGI.dta
drop _merge 

//	#5
//	Cleaning up 
gen HOM=. 
gen MARKET=.
gen GLOBAL=.

order  CID nation year regioncode regionname countrycode  region subregion ///
		 region_un region_un_sub region_wb eu oecd incomegroup ///
	HOM  rhom rfhom rmhom rhom1519 rhom2024 rhom2529 rhom3034 ///
		rhom3539 rhom4044 rhom4549 rhom5054 rhom5559 rhom6064 rhom7074 ///
	MARKET  fraser heritage rol ///
	GLOBAL  KOFGI KOFGIdf KOFGIdj KOFEcGI KOFEcGIdf KOFEcGIdj ///
		KOFTrGI KOFTrGIdf KOFTrGIdj KOFFiGI KOFFiGIdf KOFFiGIdj ///
		KOFSoGI KOFSoGIdf KOFSoGIdj KOFIpGI KOFIpGIdf KOFIpGIdj ///
		KOFInGI KOFInGIdf KOFInGIdj KOFCuGI KOFCuGIdf KOFCuGIdj ///
		KOFPoGI KOFPoGIdf KOFPoGIdj ///
	PROTECT remit private_sp public_sp edu_exp healthexpend hdi ///
	 POLITIC cont_corrupt gov_eff pol_stab regulatory ruleoflaw voice_account, first 

//	#6	 
// 	save and close 
save `file'.dta, replace 
saveold `file'-old.dta, replace v(11)
log close 
//